package com.dotflat.agreement.repository;

import com.dotflat.agreement.domain.OPAgreement;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface OPAgreementRepository extends JpaRepository<OPAgreement, String>, JpaSpecificationExecutor<OPAgreement> {


    @Query(value = "SELECT * FROM op_agreement a " +
            "WHERE (a.areaId is null OR a.areaId = :areaId) AND a.id not in " +
            "(SELECT r.agreementId FROM op_agreement_record r " +
            "  WHERE r.areaId = :areaId and r.createdBy = :staffId AND r.deleted = 0) ", nativeQuery = true)
    List<OPAgreement> findAllValidItemsByRecordNotExist(@Param("areaId") String areaId, @Param("staffId") String staffId);

}
